package work.vcloud.baseinfo.controller;

import work.vcloud.baseinfo.utils.DesUtil;
import work.vcloud.baseinfo.utils.HttpUtil;
import work.vcloud.baseinfo.utils.ImportExcelUtil;
import work.vcloud.frame.controller.BaseController;
import work.vcloud.frame.dao.OpenDao;
import work.vcloud.frame.vo.OutputVo;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import javax.annotation.Resource;

/**
 * excel导入
 * 
 * @author chenjuwei
 *
 */
@RestController
public class ImportController extends BaseController {
	private static final Logger LOGGER = LoggerFactory.getLogger(ImportController.class);
	
	@Resource
	OpenDao openDao;
	
	@Value("${api.passwordReceive}")
	private String passwordReceive;

	@Value("${auth.app_id}")
	private String appId;

	/**
	 * 校验身份证号
	 * 
	 * @return
	 */
	public String checkIdCard(String idCard) {
		Map city = new HashMap();

		city.put("11", "北京");
		city.put("12", "天津");
		city.put("13", "河北");
		city.put("14", "山西");
		city.put("15", "内蒙古");
		city.put("21", "辽宁");
		city.put("22", "吉林");
		city.put("23", "黑龙江");
		city.put("31", "上海");
		city.put("32", "江苏");
		city.put("33", "浙江");
		city.put("34", "安徽");
		city.put("35", "福建");
		city.put("36", "江西");
		city.put("37", "山东");
		city.put("41", "河南");
		city.put("42", "湖北");
		city.put("43", "湖南");
		city.put("44", "广东");
		city.put("45", "广西");
		city.put("46", "海南");
		city.put("50", "重庆");
		city.put("51", "四川");
		city.put("52", "贵州");
		city.put("53", "云南");
		city.put("54", "西藏");
		city.put("61", "陕西");
		city.put("62", "甘肃");
		city.put("63", "青海");
		city.put("64", "宁夏");
		city.put("65", "新疆");
		city.put("71", "台湾");
		city.put("81", "香港");
		city.put("82", "澳门");
		city.put("91", "国外");
		// 返回信息
		String massage = "";
		if (!Pattern.matches("^(\\d{6})(19|20)(\\d{2})(1[0-2]|0[1-9])(0[1-9]|[1-2][0-9]|3[0-1])(\\d{3})(\\d|X|x)?$",
				idCard)) {
			massage += "，格式错误";
		}
		/*
		String cityStr = idCard.substring(0,2);
		
		if(!city.containsKey(cityStr)) { massage += "，地址编码错误"; }
		

        // 系数列表
        int[] ratioArr = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};

        // 校验码列表
        char[] checkCodeList = {'1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'};

        // 获取身份证号字符数组
        char[] cIds = idCard.toCharArray();
        
		if (idCard.length() == 18) {

			// 获取最后一位（身份证校验码）
	        char oCode = cIds[17];
	        int[] iIds = new int[17];
	        int idSum = 0;// 身份证号第1-17位与系数之积的和
	        int residue = 0;// 余数(用加出来和除以11，看余数是多少？)

	        for (int i = 0; i < 17; i++) {
	            iIds[i] = cIds[i] - '0';
	            idSum += iIds[i] * ratioArr[i];
	        }

	        residue = idSum % 11;// 取得余数


			if (Character.toUpperCase(oCode) == checkCodeList[residue]) {
				
			}else {
				massage += "，校验位错误";
			}
		}
		if (!massage.equals("")) {
			return massage.substring(1);
		} else {
			return massage;
		}
		
		if (idCard.length() != 18) {
			massage = "长度必须为18位";
		}*/
		return massage;

	}
	
	public static boolean isNumeric(String str){
		for (int i = str.length();--i>=0;){
			if (!Character.isDigit(str.charAt(i))){
				return false;
			}
		}
		return true;
	}

	/**
	 * 校验手机号（0）,座机号（1）,都可以（2）
	 * 
	 * @return
	 */
	public String checkPhone(String phone, int type) {
		String massage = "";
		if (!phone.equals("")) {
//			if (type == 0) {
//				if (!Pattern.matches("^([1]\\d{10}|([\\(（]?0[0-9]{2,3}[）\\)]?[-]?)?([2-9][0-9]{6,7})+(\\-[0-9]{1,4})?)$", phone)) {
//					massage = "格式错误";
//				}
//			} else if (type == 1) {
//				if (!Pattern.matches("^0\\d{2,3}-?\\d{7,8}$", phone)) {
//					massage = "格式错误";
//				}
//			}else {
//				if (!Pattern.matches("^([1]\\d{10}|([\\(（]?0[0-9]{2,3}[）\\)]?[-]?)?([2-9][0-9]{6,7})+(\\-[0-9]{1,4})?)$", phone) && !Pattern.matches("^0\\d{2,3}-?\\d{7,8}$", phone)) {
//					massage = "格式错误";
//				}
//			}
		}
		boolean flag1 = !Pattern.matches("^((13[0-9])|(14[5,7,9])|(15([0-3]|[5-9]))|(16[5,6])|(17[0-8])|(18[0-9])|(19[1、5、8、9]))\\d{8}$", phone);
		boolean flag2 = !Pattern.matches("^(^(\\d{3,4}-)?\\d{7,8})$|(13[0-9]{9})?$", phone);
		if (flag1 && flag2) {
			massage += "，格式错误";
		}
		return massage;
	}
	
	/**
	 * 校验科学计数法
	 * 
	 * @return
	 */
	public String checkCount(String str) {
		String massage = "";
		if(str.indexOf("E")>0){
			massage = ",格式错误";
		}else if(str.indexOf(".")>0){
			massage = ",格式错误";
		}
		return massage;
	}

	/**
	 * 校验邮箱
	 * 
	 * @return
	 */
	public String checkEmail(String email) {
		String massage = "";

		if (!email.equals("")) {
			if (!Pattern.matches("^(\\w-*\\.*)+@(\\w-?)+(\\.\\w{2,})+$", email)) {
				massage = "格式错误";
			}
		}

		return massage;
	}

	/**
	 * 校验日期
	 * 
	 * @return
	 */
	public String checkDate(String date) {
		String massage = "";
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        if (!date.equals("")) {
        	try {
                format.setLenient(false);
                format.parse(date);
            }catch (Exception e){
            	e.printStackTrace();
                massage = "格式错误";
    			return massage;
            }
        }
        return massage;
	}

	/**
	 * 校验内容长度
	 * 
	 * @return
	 */
	public String checkLength(String str, String len) {

		String massage = "";

		if (!str.equals("")) {
			if (str.length() > Integer.parseInt(len)) {
				massage = "内容长度过长,无法存储";
			}
		}

		return massage;
	}
	
	/**
	 * 校验传真
	 * 
	 * @return
	 */
	public String checkFax(String fax) {
		String massage = "";
		if (!fax.equals("")) {
			if (!Pattern.matches("^0\\d{2,3}-?\\d{7,8}$", fax)) {
				massage = "格式错误";
			}
		}
		return massage;
	}

	/**
	 * 校验邮编
	 * 
	 * @return
	 */
	public String checkZipCode(String zipcode) {
		String massage = "";
		if (!zipcode.equals("")) {
			if (!Pattern.matches("^[1-9][0-9]{5}$", zipcode)) {
				massage = "格式错误";
			}
		}
		return massage;
	}
	
	/**
	 * 校验只允许填写数字
	 * 
	 * @return
	 */
	public String checkNumber(String number) {
		String massage = "";
		if (!number.equals("")) {
			if (!Pattern.matches("^$|^\\d+$|^\\d+[.]?\\d+$", number)) {
				massage = "不是数字，只允许填写数字";
			}
		}
		return massage;
	}
	
	/**
	 * 校验时间
	 * 
	 * @return
	 */
	public String checkTime(String time) {

		String massage = "";

		SimpleDateFormat sd = new SimpleDateFormat("HH:mm");// 括号内为日期格式，y代表年份，M代表年份中的月份（为避免与小时中的分钟数m冲突，此处用M），d代表月份中的天数
		if (!time.equals("")) {

			try {
				sd.setLenient(false);// 此处指定日期/时间解析是否不严格，在true是不严格，false时为严格
				sd.parse(time);// 从给定字符串的开始解析文本，以生成一个日期
			} catch (Exception e) {
				massage = "格式错误";
				return massage;
			}

		}

		return massage;
	}
	
	/**
	 * 通过名字查询外键关联表的ID
	 */
	public void selectForm(Map<String, Object> map,Object unitname) throws Exception {
		StringBuffer buffer = new StringBuffer();
		if (map.get("fk_form").toString().equals("sc_prop")) {
			buffer.append("(select prop_pk as id from baseinfo.sc_prop where status='0' and  prop_value = '" + map.get("name").toString() + "') "
					+ map.get("col"));
		} else if (map.get("fk_form").toString().equals("sc_class")) {
			buffer.append("(select id from baseinfo." + map.get("fk_form") + " where name = '"
					+ map.get("name").toString() + "' and status != '99'");
			if(unitname!=null) {
				//buffer.append("and fk_unit = (select id from baseinfo.sc_unit where name = '"+unitname+"' and status != '99')");
				if(!unitname.equals("")) {
					buffer.append("and fk_unit = '"+unitname+"'");
				}else {
					Map<String, Object> info  = (Map)session.getAttribute("userInfo");
					buffer.append("and fk_unit = '"+info.get("unitId")+"'");
				}
			}else {
				Map<String, Object> roleMap = new HashMap<>();
				roleMap.put("userInfo", session.getAttribute("userInfo"));
				roleMap.put("sqlMapId", "role.selectType");
				
				List<Map<String, Object>> roleList = openDao.queryForList(roleMap);
				
				if(roleList.size() > 0) {
					Integer result = Integer.valueOf(roleList.get(0).get("type").toString());
					
					if(result > 1 ) {
						Map<String, Object> info  = (Map)session.getAttribute("userInfo");
						buffer.append("and fk_unit = '"+info.get("unitId")+"'");
					}
				}
				
			}
			buffer.append("and academic = (select prop_pk from baseinfo.sc_prop where prop_key='STU_YEAR' and status = '0')"
					+ "and semester = (select prop_pk from baseinfo.sc_prop  where prop_key='STU_TERM' and status = '0')"
					+ ") " + map.get("col"));
		}else if(map.get("fk_form").toString().equals("sc_class_teacher")) {
			buffer.append("(select id from baseinfo.sc_user_teacher where name = '"+map.get("name").toString()+"' ");
			
			if(unitname!=null) {
				if(!unitname.equals("")) {
					buffer.append("and gzdwpk = '"+unitname+"'");
				}else {
					Map<String, Object> info  = (Map)session.getAttribute("userInfo");
					buffer.append("and gzdwpk = '"+info.get("unitId")+"'");
				}
			}
			buffer.append(" limit 1 )"+map.get("col"));
			
		}else {
			buffer.append("(select id from baseinfo." + map.get("fk_form") + " where status != '99' and (name = '"+ map.get("name").toString() + "' or id = '"+ map.get("name").toString() + "') ) " + map.get("col")+" ");
		}
		map.put("exportParams", buffer);
	}

	/**
	 * 通过名字查询外键关联表的ID
	 */
	public void selectForm_course_info(Map<String, Object> map,Object unitname) throws Exception {
		StringBuffer buffer = new StringBuffer();
		if (map.get("fk_form").toString().equals("sc_prop")) {
			buffer.append("(select prop_pk as id from baseinfo.sc_prop where  prop_value = '" + map.get("name").toString() + "') "
					+ map.get("col"));
		} else if (map.get("fk_form").toString().equals("sc_class")) {
			buffer.append("(select id from baseinfo." + map.get("fk_form") + " where name = '"
					+ map.get("name").toString() + "' and status != '99'");
			if(unitname!=null) {
				//buffer.append("and fk_unit = (select id from baseinfo.sc_unit where name = '"+unitname+"' and status != '99')");
				if(!unitname.equals("")) {
					buffer.append("and fk_unit = '"+unitname+"'");
				}else {
					Map<String, Object> info  = (Map)session.getAttribute("userInfo");
					buffer.append("and fk_unit = '"+info.get("unitId")+"'");
				}
			}
			/*buffer.append("and academic = (select prop_pk from baseinfo.sc_prop where prop_key='STU_YEAR' and status = '0')"
					+ "and semester = (select prop_pk from baseinfo.sc_prop  where prop_key='STU_TERM' and status = '0')"
					+ ") " + map.get("col"));*/

			buffer.append(") " + map.get("col"));
		}else if(map.get("fk_form").toString().equals("sc_class_teacher")) {
			buffer.append("(select id from baseinfo.sc_user_teacher where name = '"+map.get("name").toString()+"' ");

			if(unitname!=null) {
				if(!unitname.equals("")) {
					buffer.append("and gzdwpk = '"+unitname+"'");
				}else {
					Map<String, Object> info  = (Map)session.getAttribute("userInfo");
					buffer.append("and gzdwpk = '"+info.get("unitId")+"'");
				}
			}
			buffer.append(" limit 1 )"+map.get("col"));

		}else {
			buffer.append("(select id from baseinfo." + map.get("fk_form") + " where status != '99' and (name = '"+ map.get("name").toString() + "' or id = '"+ map.get("name").toString() + "') ) " + map.get("col")+" ");
		}
		map.put("exportParams", buffer);
	}



	public void selectFKForm(Map<String, Object> colMap,Map<String, Object> dataMap,String name,String col,String data,StringBuffer error) throws Exception {
		selectFKForm(colMap,dataMap,name,col,data,error, null);
	}

	/**
	 * 公用判断，外键关联表 非空 外键规则
	 * @param colMap excel表头
	 * @param dataMap excel行数据
	 * @param name excel表头名称
	 * @param col excel表头对应表字段名称
	 * @param data excel表头对应的值
	 * @param error
	 * @param moduleName
	 * @throws Exception
	 */
	public void selectFKForm(Map<String, Object> colMap,Map<String, Object> dataMap,String name,String col,String data,StringBuffer error, String moduleName) throws Exception {
		
		//如果excel中必填项为空
		if (data.equals("") && colMap.get("ifnull").equals("1")) {//必填项
			dataMap.put("userInfo", session.getAttribute("userInfo"));
			dataMap.put("sqlMapId", "role.selectType");
			
			List<Map<String, Object>> roleList = openDao.queryForList(dataMap);
			
			Map userInfo = (Map)session.getAttribute("userInfo");
			LOGGER.info("用户: " + userInfo.get("id") +  ",判断excel必填项_查询用户角色类型: " + JSONObject.toJSONString(roleList));
			
			Integer result = Integer.valueOf(roleList.get(0).get("type").toString());
			
			//如果是校级角色 并且 excel表字段是工作单位或人事单位
			if(result>1 && (colMap.get("col").equals("fk_unit") || colMap.get("col").equals("gzdwpk"))) {
				//dataMap.put("fk_unit", ((Map)session.getAttribute("userInfo")).get("unitId"));
				return;
			}
			error.append(name + "不能为空。");
		}
		if (colMap.get("fk_rule") != null) {
			if(!data.equals("")) {
				Map<String, Object> ruleMap = new HashMap<String, Object>();
				ruleMap.put("fk_rule",colMap.get("fk_rule").toString());
				ruleMap.put("name", data);
				ruleMap.put("sqlMapId", "dictionary.selectByImport");
				Map<String, Object> idMap = (Map<String, Object>)openDao.queryForObject(ruleMap);
				if (idMap == null) {
					if(colMap.get("ifnull").equals("1")) {
						error.append(name + "未录入相关信息，请先录入相关信息。");
					}
				}else {
					dataMap.put(col, idMap.get("id"));
				}
			}
		}
		if (colMap.get("fk_form") != null) {
			Map<String, Object> formMap = new HashMap<String, Object>();
			formMap.put("fk_form", colMap.get("fk_form").toString());
			formMap.put("col", col);
			if(!data.equals("")) {
				formMap.put("name", data);
			}else {
				formMap.put("name", colMap.get("name"));
			}
			if (moduleName == null){
				selectForm(formMap,dataMap.get("fk_unit"));
			}else {
				switch (moduleName){
					case "course_info":
						if(dataMap.get("fk_unit") == null){
							JSONObject userInfo = JSONObject.parseObject(JSONObject.toJSONString(dataMap.get("userInfo")));
							selectForm_course_info(formMap,userInfo.get("unitId"));
						}else{
							selectForm_course_info(formMap,dataMap.get("fk_unit"));
						}
						break;
					default:;
				}
			}

			formMap.put("sqlMapId", "port.selectImport");
			List<Map<String, Object>> idList = openDao.queryForList(formMap);
			if (idList.size()<=0) {
				if(!data.equals("")) {
					dataMap.put("userInfo", session.getAttribute("userInfo"));
					dataMap.put("sqlMapId", "role.selectType");
					Integer result = Integer.valueOf(openDao.queryForList(dataMap).get(0).get("type").toString());
					if(result>1 && (colMap.get("col").equals("fk_unit") || colMap.get("col").equals("gzdwpk"))) {
						//dataMap.put("fk_unit", ((Map)session.getAttribute("userInfo")).get("unitId"));
						return;
					}
					error.append(name + "未录入相关信息，请先录入相关信息。");
				}
			}else {
				dataMap.put(col, idList.get(0).get("id"));
			}
		}
		if (colMap.get("length") != null) {
			String result = checkLength(data, colMap.get("length").toString());
			if (!result.equals("")) {
				error.append(name + "超过数据库存储长度。\n");
			}
		}
	}

	/**
	 *查询重复行 
	 */
	public void selectRepeatRow(List<Map<String, Object>> dataList,List<Object> errorList) throws Exception {
		for (int i = 0; i < dataList.size(); i++) {
			StringBuffer error = new StringBuffer();
			Map<String, Object> dataMap1 = dataList.get(i);
			for (int j = i+1; j < dataList.size(); j++) {
				Map<String, Object> dataMap2 = dataList.get(j);
				if(dataMap1.equals(dataMap2)) {
					error.append("重复行：第"+(i+2)+"、"+(j+2)+"行。\n");
				}
			}
			if(error.length() > 0) {
				errorList.add(error);
			}
		}
	}

	/**
	 *查询重复列 
	 */
	public void selectRepeatCol(List<Map<String, Object>> dataList,List<Map<String, Object>> keyList, List<Object> errorList) throws Exception {
		for (int i = 0; i < dataList.size(); i++) {
			StringBuffer error = new StringBuffer();
			Map<String, Object> dataMap1 = dataList.get(i);
			for (int j = i+1; j < dataList.size(); j++) {
				Map<String, Object> dataMap2 = dataList.get(j);
				for (int k = 0; k < keyList.size(); k++) {
					Map<String, Object> keyMap = keyList.get(k);
					if(dataMap1.get(keyMap.get("col"))!=null&&dataMap1.get(keyMap.get("col"))!=""&&dataMap2.get(keyMap.get("col"))!=null&&dataMap2.get(keyMap.get("col"))!="") {
						if(dataMap1.get(keyMap.get("col")).toString().equals(dataMap2.get(keyMap.get("col")).toString())) {
							error.append(keyMap.get("name")+"重复：第"+(i+2)+"、"+(j+2)+"行。\n");
						}
					}
				}
			}
			if(error.length() > 0) {
				errorList.add(error);
			}
		}
	}
	
	/**
	 * 导入学生信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/student.import")
	public OutputVo studentImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);
			Map<String, Object> map = getParameterMap();
			map.remove("page");

			Object userInfoObj = session.getAttribute("userInfo");
			Map<String, Object> userInfo = (Map)userInfoObj;
			String sessionId = session.getId();
			LOGGER.info(sessionId + " 用户: " + userInfo.get("id") + "正在导入学生数据...");
			LOGGER.info(sessionId + " 用户信息: " + JSONObject.toJSONString(userInfo));
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			//查询表名为sc_user_student的excel字段配置
			map.put("form", "sc_user_student");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			//错误列表，若有错误，则返回此列表
			List<Object> errorList = new ArrayList<>();
			
			//ifsole 是否唯一 
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			
			selectRepeatCol(dataList, keyList, errorList);
			
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
					if (col.equals("sfzjh")) {
						String result = checkIdCard(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
						map.put("sfzjh", data);
						map.put("sqlMapId", "student.selectIdCard");
						Integer result1 = (Integer) openDao.queryForObject(map);
						if (result1 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("xjzh")) {
						map.put("xjzh", data);
						map.put("sqlMapId", "student.selectStudentCode");
						Integer result2 = (Integer) openDao.queryForObject(map);
						if (result2 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("yhm")) {
						if (!data.equals("")) {
							map.put("yhm", data);
							map.put("sqlMapId", "student.selectYhm");
							Integer result1 = (Integer) openDao.queryForObject(map);
							if (result1 > 0) {
								error.append(name + "重复。");
							}
						}
					}else if (col.equals("email")) {
//						String result = checkEmail(data);
//						if (!result.equals("")) {
//							error.append(name + result + "。");
//						}
					} else if (col.equals("lxdh")) {
						String result = checkPhone(data,2);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
					}else if (col.equals("csrq")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("zcsj")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("zwzcsj")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			
			List<Map<String, String>> pwdList= new ArrayList<Map<String, String>>();
			
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> student = dataList.get(i);
					student.put("userInfo", userInfoObj);

					if(!session.getId().equals(sessionId)){
						Object userInfoCheck = session.getAttribute("userInfo");
						LOGGER.info("sessionId不同,原sessionId: [" + sessionId + "], userInfo: ["+ JSONObject.toJSONString(userInfo)+"], 现sessionId:"+ session.getId()+" userInfo: [" + JSONObject.toJSONString(userInfoCheck) +"]");
					}

					if(result>1) {
						student.put("fk_unit","self");
					}
					if(student.get("id")!=null) {
						student.put("fk_user", student.get("id").toString());
					}
					// 插入用户
//					student.put("type", "2");
//					student.put("sqlMapId", "user.insert");
//					if(student.get("id")==null) {
//						student.put("fk_user", openDao.insert(student));
//					}else {
//						openDao.insert(student);
//					}
					// 插入学生
					student.put("state","00000XSZTM2011040108545142173520036");
					student.put("sqlMapId", "student.insert");
					student.put("fk_user", openDao.insert(student));

					//添加学生角色
					student.put("sqlMapId", "role.insertUserRole");
					student.put("fk_role","c0994f3c-3849-44c5-bd46-85ce49867e26");
					openDao.insert(student);

					if(dataList.get(i).get("password")!=null) {
						Map<String, String> pwd = new HashMap<String,String>();
						pwd.put("id", student.get("fk_user").toString());
						String passwd = dataList.get(i).get("password").toString();
						pwd.put("password", DesUtil.getEncryptionPwd(passwd));
						pwdList.add(pwd);
					}
				}
				
				if(pwdList.size() > 0) {
					JSONObject jsonObject = new JSONObject();
					jsonObject.put("pwdList", pwdList);
					jsonObject.put("type", 3);
					String accessToken = ((Map)session.getAttribute(appId)).get("access_token").toString();
					HttpUtil.doPost2(passwordReceive, jsonObject, accessToken);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入学生信息(修改)
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/student.importUpdate")
	public OutputVo studentImportUpdate(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);
			Map<String, Object> map = getParameterMap();
			map.remove("page");

			Object userInfoObj = session.getAttribute("userInfo");
			Map<String, Object> userInfo = (Map)userInfoObj;
			String sessionId = session.getId();
			LOGGER.info(sessionId + " 用户: " + userInfo.get("id") + "正在覆盖导入学生数据...");
			LOGGER.info(sessionId + " 用户信息: " + JSONObject.toJSONString(userInfo));
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			//查询学生表excel配置
			map.put("form", "sc_user_student");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			//必须配置字段
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			
			//查询重复列
			selectRepeatCol(dataList, keyList, errorList);
			
			//循环excel中的数据 相当于循环excel的行
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				
				//excel中的一行数据 key为表字段名称
				Map<String, Object> dataMap = dataList.get(i);
				
				//相当于循环excel的表头
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 excel表头名字
					String col = colMap.get("col").toString();// 列名 表字段名称
					
					String data = "";
					if (dataMap.get(col) != null) { //excel中这一列这一行的值不为空
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					
					//判断必填项
					selectFKForm(colMap,dataMap,name,col,data,error);
					
					//判断字段在数据库中的重复数据
					if (col.equals("sfzjh")) {
						String result = checkIdCard(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
						map.put("sfzjh", data);
						map.put("sqlMapId", "student.selectIdCardInfo");
						List<Map<String, Object>> resultList = openDao.queryForList(map);
						if(dataList.get(i).get("id")!=null) {
							if(resultList.size()>0) {
								if(!resultList.get(0).get("id").toString().equals(dataList.get(i).get("id").toString())) {
									error.append(name + "重复。");
								}
							}
						}else {
							if(resultList.size()>0) {
								error.append(name + "重复。");
							}
						}
					}else if (col.equals("xjzh")) {
						map.put("xjzh", data);
						map.put("sqlMapId", "student.selectStudentCodeInfo");
						List<Map<String, Object>> resultList = openDao.queryForList(map);
						if(dataList.get(i).get("id")!=null) {
							if(resultList.size()>0) {
								if(!resultList.get(0).get("id").toString().equals(dataList.get(i).get("id").toString())) {
									error.append(name + "重复。");
								}
							}
						}else {
							if(resultList.size()>0) {
								error.append(name + "重复。");
							}
						}
					}else if (col.equals("yhm")) {
						if (!data.equals("")) {
							map.put("yhm", data);
							map.put("sqlMapId", "student.selectYhmInfo");
							List<Map<String, Object>> resultList = openDao.queryForList(map);
							if(dataList.get(i).get("id")!=null) {
								if(resultList.size()>0) {
									if(!resultList.get(0).get("id").toString().equals(dataList.get(i).get("id").toString())) {
										error.append(name + "重复。");
									}
								}
							}else {
								if(resultList.size()>0) {
									error.append(name + "重复。");
								}
							}
						}
					}else if (col.equals("email")) {
//						String result = checkEmail(data);
//						if (!result.equals("")) {
//							error.append(name + result + "。");
//						}
					} else if (col.equals("lxdh")) {
						String result = checkPhone(data,2);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
					}else if (col.equals("csrq")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("zcsj")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("zwzcsj")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			
			List<Map<String, String>> pwdList= new ArrayList<Map<String, String>>();
			
			//如果错误为0
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> student = dataList.get(i);

					if(!session.getId().equals(sessionId)){
						Object userInfoCheck = session.getAttribute("userInfo");
						LOGGER.info("sessionId不同,原sessionId: [" + sessionId + "], userInfo: ["+ JSONObject.toJSONString(userInfo)+"], 现sessionId:"+ session.getId()+" userInfo: [" + JSONObject.toJSONString(userInfoCheck) +"]");
					}

					student.put("userInfo", userInfoObj);
					
					//校级用户 设置学生单位为与教师同一单位
					if(result>1) {
						student.put("fk_unit","self");
					}
					if(student.get("id")!=null) {
						student.put("fk_user", student.get("id").toString());
					}
					
					// 向user表插入数据
//					student.put("type", "2");
//					student.put("sqlMapId", "user.insert");
//					if(student.get("id")==null) {
//						student.put("fk_user", openDao.insert(student));
//					}else {
//						Map<String, Object> queryMap = new HashMap<String, Object>();
//						queryMap.put("id", student.get("id").toString());
//						queryMap.put("sqlMapId", "user.selectById");
//						Map<String, Object> userMap=(Map<String, Object>)openDao.queryForObject(queryMap);
//						if(userMap!=null) {
//							student.put("id", student.get("id").toString());
//							student.put("sqlMapId", "user.update");
//							openDao.update(student);
//						}else {
//							openDao.insert(student);
//						}
//					}
					
					//向学生表插入数据
					//新学生数据 新增
					if(student.get("id")==null) {
						// 插入学生
						student.put("state","00000XSZTM2011040108545142173520036");
						student.put("sqlMapId", "student.insert");
						String studentId = openDao.insert(student).toString();
						student.put("id", studentId);
						LOGGER.info(sessionId + " 教师: " + userInfo.get("id") + ", 单位: " + userInfo.get("unitId") + ", 导入学生: " + JSONObject.toJSONString(student) + ", 结果: " + studentId);

						//添加学生角色
						student.put("fk_user", studentId);
						student.put("sqlMapId", "role.insertUserRole");
						student.put("fk_role","c0994f3c-3849-44c5-bd46-85ce49867e26");
						openDao.insert(student);
					}else {
						Map<String, Object> queryMap = new HashMap<String, Object>();
						queryMap.put("id", student.get("id").toString());
						queryMap.put("sqlMapId", "student.selectByIdAndStatus");
						Map<String, Object> studentMap=(Map<String, Object>)openDao.queryForObject(queryMap);
						if(studentMap!=null) {
							student.put("state","00000XSZTM2011040108545142173520036");
							student.put("id", student.get("id").toString());
							student.put("sqlMapId", "student.update");
							int num = openDao.update(student);
							
							LOGGER.info( sessionId + " 教师: " + userInfo.get("id") + ", 单位: " + userInfo.get("unitId") + ", 导入学生更新: " + JSONObject.toJSONString(student) + ", 结果: " + num);

						}else {
							// 插入学生
							student.put("state","00000XSZTM2011040108545142173520036");
							student.put("sqlMapId", "student.insert");
							String studentId = openDao.insert(student).toString();
							student.put("id", studentId);
							LOGGER.info(sessionId + " 教师: " + userInfo.get("id") + ", 单位: " + userInfo.get("unitId") + ", 导入学生: " + JSONObject.toJSONString(student) + ", 结果: " + studentId);

							//添加学生角色
							student.put("fk_user", studentId);
							student.put("sqlMapId", "role.insertUserRole");
							student.put("fk_role","c0994f3c-3849-44c5-bd46-85ce49867e26");
							openDao.insert(student);
						}
					}
					
					
					if(dataList.get(i).get("password")!=null) {
						Map<String, String> pwd=new HashMap<String,String>();
						pwd.put("id", student.get("id").toString());
						String passwd = dataList.get(i).get("password").toString();
						pwd.put("password", DesUtil.getEncryptionPwd(passwd));
						pwdList.add(pwd);
					}
				}
				
				if(pwdList.size() > 0) {
					JSONObject jsonObject = new JSONObject();
					jsonObject.put("pwdList",pwdList);
					jsonObject.put("type", 3);
					String accessToken = ((Map)session.getAttribute(appId)).get("access_token").toString();
					HttpUtil.doPost2(passwordReceive, jsonObject, accessToken);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}


	/**
	 * 导入学生信息（学籍证号和电子注册号）
	 *
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/student.Updatexj")
	public OutputVo studentUpdatexj(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);
			Map<String, Object> map = getParameterMap();
			map.remove("page");

			Object userInfoObj = session.getAttribute("userInfo");
			Map<String, Object> userInfo = (Map)userInfoObj;
			String sessionId = session.getId();
			LOGGER.info(sessionId + " 用户: " + userInfo.get("id") + "正在覆盖导入学生数据...");
			LOGGER.info(sessionId + " 用户信息: " + JSONObject.toJSONString(userInfo));

			InputStream in = null;

			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			//查询学生表excel配置
			map.put("form", "sc_user_student");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);



			for(int i = 0; i < dataList.size(); i++){
				Map<String, Object> studentMap = dataList.get(i);

				map.put("sfzjh",studentMap.get("sfzjh"));
				map.put("dzzch",studentMap.get("dzzch"));
				map.put("xjzh",studentMap.get("xjzh"));
				map.put("sqlMapId", "student.updateBySfz");
				openDao.update(map);

			}


			return output("0000");
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}



	/**
	 * 导入教师信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/teacher.import")
	public OutputVo teacherImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_user_teacher");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
		
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			selectRepeatCol(dataList, keyList, errorList);
			
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
					
					if (col.equals("sfzjh")) {
						String result = checkIdCard(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
						map.put("sfzjh", data);
						map.put("sqlMapId", "teacher.selectIdCard");
						Integer result1 = (Integer) openDao.queryForObject(map);
						if (result1 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("phone")) {
						String result = checkPhone(data,0);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
						map.put("phone", data);
						map.put("sqlMapId", "teacher.selectPhone");
						Integer result1 = (Integer) openDao.queryForObject(map);
						if (result1 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("yhm")) {
						if (!data.equals("")) {
							map.put("yhm", data);
							map.put("sqlMapId", "teacher.selectYhm");
							Integer result1 = (Integer) openDao.queryForObject(map);
							if (result1 > 0) {
								error.append(name + "重复。");
							}
						}
					}else if (col.equals("email")) {
//						String result = checkEmail(data);
//						if (!result.equals("")) {
//							error.append(name + result + "。");
//						}
					}else if (col.equals("jgprny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("gzny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("cjny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("lxny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("xzzwrzny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("zjzwrzny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("zjzwzgqdny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			
			List<Map<String, String>> pwdList= new ArrayList<Map<String, String>>();
			
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> teacher = dataList.get(i);
					teacher.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						teacher.put("gzdwpk","self");
					}
//					if(teacher.get("id")!=null) {
//						teacher.put("fk_user", teacher.get("id").toString());
//					}
//					// 插入用户
//					teacher.put("type", "1");
//					teacher.put("sqlMapId", "user.insert");
//					if(teacher.get("id")==null) {
//						teacher.put("fk_user", openDao.insert(teacher));
//					}else {
//						openDao.insert(teacher);
//					}
					
					// 插入教师
					teacher.put("sqlMapId", "teacher.insert");
					teacher.put("fk_user", openDao.insert(teacher));
					if(dataList.get(i).get("password")!=null) {
						Map<String, String> pwd=new HashMap<String,String>();
						pwd.put("id", teacher.get("fk_user").toString());
						String passwd = dataList.get(i).get("password").toString();
						pwd.put("password", DesUtil.getEncryptionPwd(passwd));
						pwdList.add(pwd);
					}
					
					
					//添加教师权限
					teacher.put("sqlMapId", "role.insertUserRole");
					teacher.put("fk_role","ace0c584-7940-45fe-a3ef-7f7f3f3b559f");
					openDao.insert(teacher);
					
					
					//添加单位关系
					Map userunit = new HashMap();
					userunit.put("sqlMapId", "userunit.insert");
					userunit.put("fk_user",teacher.get("fk_user"));
					userunit.put("type","1");
					userunit.put("gzdwpk", teacher.get("gzdwpk"));
					userunit.put("fk_unit",teacher.get("gzdwpk"));
					openDao.insert(userunit);
					
					
					userunit.put("sqlMapId", "userunit.insert");
					userunit.put("fk_user",teacher.get("fk_user"));
					userunit.put("type","2");
					userunit.put("rsdwpk", teacher.get("rsdwpk"));
					userunit.put("fk_unit",teacher.get("rsdwpk"));
					openDao.insert(userunit);

				}
				if(pwdList.size() > 0) {
					JSONObject jsonObject = new JSONObject();
					jsonObject.put("pwdList", pwdList);
					jsonObject.put("type", 3);
					String accessToken = ((Map)session.getAttribute(appId)).get("access_token").toString();
					HttpUtil.doPost2(passwordReceive, jsonObject, accessToken);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入教师信息(带修改)
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/teacher.importUpdate")
	public OutputVo teacherImportUpdate(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_user_teacher");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
		
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			selectRepeatCol(dataList, keyList, errorList);
			
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
					
					if (col.equals("sfzjh")) {
						String result = checkIdCard(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
						
						map.put("sfzjh", data);
						map.put("sqlMapId", "teacher.selectIdCardInfo");
						List<Map<String, Object>> resultList = openDao.queryForList(map);
						if(dataList.get(i).get("id")!=null) {
							if(resultList.size()>0) {
								if(!resultList.get(0).get("id").toString().equals(dataList.get(i).get("id").toString())) {
									error.append(name + "重复。");
								}
							}
						}else {
							if(resultList.size()>0) {
								error.append(name + "重复。");
							}
						}
					}else if (col.equals("phone")) {
						String result = checkPhone(data,0);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
						map.put("phone", data);
						map.put("sqlMapId", "teacher.selectPhoneInfo");
						List<Map<String, Object>> resultList = openDao.queryForList(map);
						if(dataList.get(i).get("id")!=null) {
							if(resultList.size()>0) {
								if(!resultList.get(0).get("id").toString().equals(dataList.get(i).get("id").toString())) {
									error.append(name + "重复。");
								}
							}
						}else {
							if(resultList.size()>0) {
								error.append(name + "重复。");
							}
						}
					}else if (col.equals("yhm")) {
						if (!data.equals("")) {
							map.put("yhm", data);
							map.put("sqlMapId", "teacher.selectYhmInfo");
							List<Map<String, Object>> resultList = openDao.queryForList(map);
							if(dataList.get(i).get("id")!=null) {
								if(resultList.size()>0) {
									if(!resultList.get(0).get("id").toString().equals(dataList.get(i).get("id").toString())) {
										error.append(name + "重复。");
									}
								}
							}else {
								if(resultList.size()>0) {
									error.append(name + "重复。");
								}
							}
						}
					}else if (col.equals("email")) {
//						String result = checkEmail(data);
//						if (!result.equals("")) {
//							error.append(name + result + "。");
//						}
					}else if (col.equals("jgprny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("gzny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("cjny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("lxny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("xzzwrzny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("zjzwrzny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("zjzwzgqdny")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			
			List<Map<String, String>> pwdList= new ArrayList<Map<String, String>>();
			
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> teacher = dataList.get(i);
					teacher.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						teacher.put("gzdwpk","self");
					}
					if(teacher.get("id")!=null) {
						teacher.put("fk_user", teacher.get("id").toString());
					}
//					// 插入用户
//					teacher.put("type", "1");
//					teacher.put("sqlMapId", "user.insert");
//					if(teacher.get("id")==null) {
//						teacher.put("fk_user", openDao.insert(teacher));
//					}else {
//						Map<String, Object> queryMap = new HashMap<String, Object>();
//						queryMap.put("id", teacher.get("id").toString());
//						queryMap.put("sqlMapId", "user.selectById");
//						Map<String, Object> userMap=(Map<String, Object>)openDao.queryForObject(queryMap);
//						if(userMap!=null) {
//							teacher.put("id", teacher.get("id").toString());
//							teacher.put("sqlMapId", "user.update");
//							openDao.update(teacher);
//						}else {
//							openDao.insert(teacher);
//						}
//					}
					
					if(teacher.get("id")==null) {
						// 插入教师
						teacher.put("sqlMapId", "teacher.insert");
						teacher.put("fk_user", openDao.insert(teacher));
						
						//添加教师权限
						teacher.put("sqlMapId", "role.insertUserRole");
						teacher.put("fk_role","ace0c584-7940-45fe-a3ef-7f7f3f3b559f");
						openDao.insert(teacher);
						
						
						//添加单位关系
						Map userunit = new HashMap();
						userunit.put("sqlMapId", "userunit.insert");
						userunit.put("fk_user",teacher.get("fk_user"));
						userunit.put("type","1");
						userunit.put("gzdwpk", teacher.get("gzdwpk"));
						userunit.put("fk_unit",teacher.get("gzdwpk"));
						openDao.insert(userunit);
						
						
						userunit.put("sqlMapId", "userunit.insert");
						userunit.put("fk_user",teacher.get("fk_user"));
						userunit.put("type","2");
						userunit.put("rsdwpk", teacher.get("rsdwpk"));
						userunit.put("fk_unit",teacher.get("rsdwpk"));
						openDao.insert(userunit);
						
					}else {
						Map<String, Object> queryMap = new HashMap<String, Object>();
						queryMap.put("id", teacher.get("id").toString());
						queryMap.put("sqlMapId", "teacher.selectByIdAndStatus");
						Map<String, Object> teacherMap=(Map<String, Object>)openDao.queryForObject(queryMap);
						if(teacherMap!=null) {
							teacher.put("id", teacher.get("id").toString());
							teacher.put("sqlMapId", "teacher.update");
							openDao.update(teacher);
						}else {
							// 插入教师
							teacher.put("sqlMapId", "teacher.insert");
							teacher.put("fk_user", openDao.insert(teacher));
						}
					}
					
					
					
					if(dataList.get(i).get("password")!=null) {
						Map<String, String> pwd=new HashMap<String,String>();
						pwd.put("id", teacher.get("fk_user").toString());
						String passwd = dataList.get(i).get("password").toString();
						pwd.put("password", DesUtil.getEncryptionPwd(passwd));
						pwdList.add(pwd);
					}
					
				}
				if(pwdList.size()>0) {
					JSONObject jsonObject=new JSONObject();
					jsonObject.put("pwdList",pwdList);
					jsonObject.put("type", 3);
					String accessToken = ((Map)session.getAttribute(appId)).get("access_token").toString();
					HttpUtil.doPost2(passwordReceive, jsonObject, accessToken);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入主管单位信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/center.import")
	public OutputVo centerImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_unit_center");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			selectRepeatCol(dataList, keyList, errorList);
			
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
					
					if (col.equals("name")) {
						map.put("name", data);
						map.put("sqlMapId", "unit.selectCenterExit");
						Integer result1 = (Integer) openDao.queryForObject(map);
						if (result1 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("zip_code")) {
						String result = checkZipCode(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("fax")) {
						String result = checkFax(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("tel")) {
						String result = checkPhone(data,1);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
					}else if (col.equals("email")) {
//						String result = checkEmail(data);
//						if (!result.equals("")) {
//							error.append(name + result + "。");
//						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> ceneter = dataList.get(i);
					ceneter.put("userInfo", session.getAttribute("userInfo"));
					if(ceneter.get("id")!=null) {
						ceneter.put("fk_unit", ceneter.get("id").toString());
					}
					// 插入单位
					ceneter.put("type", "0");
					ceneter.put("sqlMapId", "unit.insertUnit");
					if(ceneter.get("id")==null) {
						ceneter.put("fk_unit", openDao.insert(ceneter));
					}else {
						openDao.insert(ceneter);
					}
					// 插入主管单位
					ceneter.put("sqlMapId", "unit.insertCenter");
					openDao.insert(ceneter);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入学校信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/school.import")
	public OutputVo schoolImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_unit_school");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			selectRepeatCol(dataList, keyList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
					
					if (col.equals("name")) {
						map.put("name", data);
						map.put("sqlMapId", "unit.selectSchoolExit");
						Integer result1 = (Integer) openDao.queryForObject(map);
						if (result1 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("zip_code")) {
						String result = checkZipCode(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("fax")) {
						String result = checkFax(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("tel")) {
						String result = checkPhone(data,1);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
					}else if (col.equals("constructed_date")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("celebrate_date_start")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("celebrate_date_end")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("primary_in_age")) {
						String result = checkNumber(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("middle_in_age")) {
						String result = checkNumber(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("people_number")) {
						String result = checkNumber(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> school = dataList.get(i);
					school.put("userInfo", session.getAttribute("userInfo"));
					if(school.get("id")!=null) {
						school.put("fk_unit", school.get("id").toString());
					}
					// 插入单位
					school.put("type", "1");
					school.put("sqlMapId", "unit.insertUnit");
					if(school.get("id")==null) {
						school.put("fk_unit", openDao.insert(school));
					}else {
						openDao.insert(school);
					}
					// 插入主管单位
					school.put("sqlMapId", "unit.insertSchool");
					openDao.insert(school);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	/**
	 * 导入分校信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/attach.import")
	public OutputVo attachImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_unit_school_attach");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
		
			map.put("ifsole", "1");
			List<Map<String, Object>> keyList = openDao.queryForList(map);
			selectRepeatCol(dataList, keyList, errorList);
			
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
						
					selectFKForm(colMap,dataMap,name,col,data,error);
					
					if (col.equals("name")) {
						map.put("name", data);
						map.put("sqlMapId", "unit.selectAttachExit");
						Integer result1 = (Integer) openDao.queryForObject(map);
						if (result1 > 0) {
							error.append(name + "重复。");
						}
					}else if (col.equals("zip_code")) {
						String result = checkZipCode(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("fax")) {
						String result = checkFax(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("tel")) {
						String result = checkPhone(data,1);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}else {
							result = checkCount(data);
							if (!result.equals("")) {
								error.append(name + result + "。");
							}
						}
					}else if (col.equals("constructed_date")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("celebrate_date_start")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("celebrate_date_end")) {
						String result = checkDate(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("primary_in_age")) {
						String result = checkNumber(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("middle_in_age")) {
						String result = checkNumber(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}else if (col.equals("people_number")) {
						String result = checkNumber(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> attach = dataList.get(i);
					attach.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						attach.put("fk_unit","self");
					}
					if(attach.get("id")!=null) {
						attach.put("id", attach.get("id").toString());
					}
					// 插入单位
					attach.put("type", "2");
					attach.put("sqlMapId", "unit.insertUnit");
					if(attach.get("id")==null) {
						attach.put("id", openDao.insert(attach));
					}else {
						openDao.insert(attach);
					}
					// 插入主管单位
					attach.put("sqlMapId", "unit.insertAttach");
					openDao.insert(attach);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入年级信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/grade.import")
	public OutputVo gradeImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_unit_grade_relate");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("sqlMapId", "unit_grade.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> grade = dataList.get(i);
					grade.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						grade.put("fk_unit","self");
					}
					if(grade.get("id")!=null) {
						grade.put("id", grade.get("id").toString());
					}
					// 导入年级
					grade.put("sqlMapId", "unit_grade.insert");
					openDao.insert(grade);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}

	/**
	 * 导入班级信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/class.import")
	public OutputVo classImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_class");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				if(dataMap.get("major")!=null) {
					dataMap.put("name", dataMap.get("fk_grade").toString()+dataMap.get("major").toString()+"("+dataMap.get("sort").toString()+")班");
				}else {
					dataMap.put("name", dataMap.get("fk_grade").toString()+"("+dataMap.get("sort").toString()+")班");
				}
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
				}
//				if (!dataMap.isEmpty()) {
//					dataMap.put("sqlMapId", "class.selectIfExit");
//					Integer result = (Integer) openDao.queryForObject(dataMap);
//					if (result > 0) {
//						error.append("与数据库已有数据重复。");
//					}
//				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> classs = dataList.get(i);
					classs.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						classs.put("fk_unit","self");
					}
					if(classs.get("id")!=null) {
						classs.put("id", classs.get("id").toString());
					}
					// 导入班级
					classs.put("sqlMapId", "class.insert");
					
					classs.put("fk_class", openDao.insert(classs));
					
					//添加班主任(正)
					if(classs.containsKey("zbzr")) {
						classs.put("fk_teacher", classs.get("zbzr"));
						classs.put("type","1");
						classs.put("sqlMapId", "class.insertClassTeacher");
						openDao.insert(classs);
					}
					
					
					//添加班主任(副)
					if(classs.containsKey("fbzr")) {
						classs.put("fk_teacher", classs.get("fbzr"));
						classs.put("type", "2");
						classs.put("sqlMapId", "class.insertClassTeacher");
						openDao.insert(classs);
					}
					
					
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入课程
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/course.import")
	public OutputVo courseImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_course");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("sqlMapId", "course.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> course = dataList.get(i);
					course.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						course.put("fk_unit","self");
						course.put("flag","2");
					}
					if(course.get("id")!=null) {
						course.put("id", course.get("id").toString());
					}
					// 导入课程
					course.put("sqlMapId", "course.insert");
					openDao.insert(course);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入上课时间
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/course_time.import")
	public OutputVo courseTimeImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_course_time");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
					if (col.equals("start_time")) {
						String result = checkTime(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					} else if (col.equals("end_time")) {
						String result = checkTime(data);
						if (!result.equals("")) {
							error.append(name + result + "。");
						}
					}
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("sqlMapId", "course_time.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> course_time = dataList.get(i);
					course_time.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						course_time.put("fk_unit","self");
					}
					if(course_time.get("id")!=null) {
						course_time.put("id", course_time.get("id").toString());
					}
					// 插入上课时间课
					course_time.put("sqlMapId", "course_time.insert");
					openDao.insert(course_time);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入任课信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/course_info.import")
	public OutputVo courseInfoImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_course_info");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error, "course_info");
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("sqlMapId", "course_info.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				map.put("sqlMapId", "role.selectType");
				Integer result = Integer.valueOf(openDao.queryForList(map).get(0).get("type").toString());
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> course_info = dataList.get(i);
					course_info.put("userInfo", session.getAttribute("userInfo"));
					if(result>1) {
						course_info.put("fk_unit","self");
					}
					if(course_info.get("id")!=null) {
						course_info.put("id", course_info.get("id").toString());
					}
					// 导入任课信息
					course_info.put("sqlMapId", "course_info.insert");
					openDao.insert(course_info);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	
	/**
	 * 导入excel配置信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/port.import")
	public OutputVo portImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			List<Map<String, Object>> colList = new ArrayList<Map<String, Object>>();
			Map<String, Object> col1 = new HashMap<String, Object>();
			col1.put("col","id");
			col1.put("name","编号（导出配置表）");
			colList.add(col1);
			
			Map<String, Object> col2 = new HashMap<String, Object>();
			col2.put("col","form");
			col2.put("name","表名");
			colList.add(col2);
			
			Map<String, Object> col3 = new HashMap<String, Object>();
			col3.put("col","col");
			col3.put("name","列");
			colList.add(col3);
			
			Map<String, Object> col4 = new HashMap<String, Object>();
			col4.put("col","name");
			col4.put("name","列名");
			colList.add(col4);
			
			Map<String, Object> col5 = new HashMap<String, Object>();
			col5.put("col","type");
			col5.put("name","类型(例如：varchar|int）");
			colList.add(col5);
			
			Map<String, Object> col6 = new HashMap<String, Object>();
			col6.put("col","length");
			col6.put("name","长度");
			colList.add(col6);
			
			Map<String, Object> col7 = new HashMap<String, Object>();
			col7.put("col","ifnull");
			col7.put("name","是否可以为空 0可空 1不可空");
			colList.add(col7);
			
			Map<String, Object> col8 = new HashMap<String, Object>();
			col8.put("col","ifexport");
			col8.put("name","是否导出 0不导 1导出");
			colList.add(col8);
			
			Map<String, Object> col9 = new HashMap<String, Object>();
			col9.put("col","fk_rule");
			col9.put("name","规则编号");
			colList.add(col9);
			
			Map<String, Object> col10 = new HashMap<String, Object>();
			col10.put("col","sort");
			col10.put("name","排序");
			colList.add(col10);
			
			Map<String, Object> col11 = new HashMap<String, Object>();
			col11.put("col","fk_form");
			col11.put("name","外键关联表");
			colList.add(col11);
			
			Map<String, Object> col12 = new HashMap<String, Object>();
			col12.put("col","ifimport");
			col12.put("name","是否导入 0不导 1导出");
			colList.add(col12);
			
			Map<String, Object> col13 = new HashMap<String, Object>();
			col13.put("col","ifsole");
			col13.put("name","是否唯一");
			colList.add(col13);
			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("sqlMapId", "port.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> port = dataList.get(i);
					port.put("userInfo", session.getAttribute("userInfo"));
					
					if(port.get("id")!=null) {
						port.put("id", port.get("id").toString());
					}
					// 导入任课信息
					port.put("sqlMapId", "port.insert");
					openDao.insert(port);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入菜单信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/menu.import")
	public OutputVo menuImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_menu");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
				}
				if (!dataMap.isEmpty()) {
					if(dataMap.get("fk_client")== null) {
						dataMap.put("fk_client", jsonData.get("fk_client"));
					}
					dataMap.put("sqlMapId", "menu.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> menu = dataList.get(i);
					menu.put("userInfo", session.getAttribute("userInfo"));
					
					if(menu.get("id")!=null) {
						menu.put("id", menu.get("id").toString());
					}
					// 导入任课信息
					menu.put("sqlMapId", "menu.insert");
					openDao.insert(menu);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导入按钮信息
	 * 
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/button.import")
	public OutputVo buttonImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);Map<String, Object> map = getParameterMap();map.remove("page");
			
			InputStream in = null;
			
			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			
			map.put("form", "sc_button");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);
			
			List<Object> errorList = new ArrayList<>();
			
			selectRepeatRow(dataList, errorList);
		
			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("sqlMapId", "button.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> button = dataList.get(i);
					button.put("userInfo", session.getAttribute("userInfo"));
					
					if(button.get("id")!=null) {
						button.put("id", button.get("id").toString());
					}
					// 导入任课信息
					button.put("sqlMapId", "button.insert");
					openDao.insert(button);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}



	/**
	 * 导入新生名单
	 *
	 * @param multipartFile
	 * @return
	 */
	@RequestMapping("api/newstudent.import")
	public OutputVo newstudentImport(@RequestParam("upfile") MultipartFile multipartFile, @RequestParam("formData") String formData) {
		try {
			JSONObject jsonData = (JSONObject) JSON.parse(formData);
			Map<String, Object> map = getParameterMap();
			map.remove("page");

			InputStream in = null;

			if (multipartFile.isEmpty()) {
				throw new Exception("文件不存在！");
			}
			in = multipartFile.getInputStream();

			String year = jsonData.get("year").toString();
			map.put("form", "sc_user_new_student");
			map.put("sqlMapId", "port.select");
			List<Map<String, Object>> colList = openDao.queryForList(map);

			// 获取excel 数据
			List<Map<String, Object>> dataList = new ImportExcelUtil().readExcel(in, colList);

			List<Object> errorList = new ArrayList<>();

			selectRepeatRow(dataList, errorList);

			for (int i = 0; i < dataList.size(); i++) {
				StringBuffer error = new StringBuffer();
				Map<String, Object> dataMap = dataList.get(i);
				for (int j = 0; j < colList.size(); j++) {
					Map<String, Object> colMap = colList.get(j);
					String name = colMap.get("name").toString();// 名称 表头名字
					String col = colMap.get("col").toString();// 列名 字段
					String data = "";
					if (dataMap.get(col) != null) {
						data = dataMap.get(col).toString();// 列对应值 字段的值
					}
					selectFKForm(colMap,dataMap,name,col,data,error);
				}
				if (!dataMap.isEmpty()) {
					dataMap.put("fk_user",dataMap.get("fk_user"));
					dataMap.put("sqlMapId", "newStudent.selectIfExit");
					Integer result = (Integer) openDao.queryForObject(dataMap);
					if (result > 0) {
						error.append("与数据库已有数据重复。");
					}
				}
				if(error.length()>0) {
					error.insert(0, "第" + (i + 2) + "行：");
					errorList.add(error);
				}
			}
			if (errorList.size() == 0) {
				for (int i = 0; i < dataList.size(); i++) {
					Map<String, Object> student = dataList.get(i);


					student.put("id",student.get("fk_user"));
					student.put("yhm",student.get("fk_user"));
					student.put("year",year);
					// 导入任课信息
					student.put("sqlMapId", "newStudent.insert");
					openDao.insert(student);
				}
				return output("0000");
			} else {
				return output("4444", "", errorList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}




}
